using System;
using System.Collections.Generic;
using System.Linq;

namespace LuyenThiVer03.UserData
{
    public class DBHelper
    {
        private const string StrConnectionString = @"isostore:/UserDB.sdf";

        public static void CreateDatabase()
        {
            using (var userDb = new UserDatabase(StrConnectionString))
            {
                if (!userDb.DatabaseExists())
                {
                    userDb.CreateDatabase();
                }
                else
                {
                    //userDb.DeleteDatabase();
                    //userDb.CreateDatabase();
                }
            }
        }

        public static DeThi getDeThi(int dethi_ID)
        {
            DeThi dethi1 = new DeThi();

            using (UserDatabase ud = new UserDatabase(StrConnectionString))
            {
                IQueryable<DeThi> dethiQuery = from dt in ud.DeThiTable where dt.DeThiID == dethi_ID select dt;
                dethi1 = dethiQuery.FirstOrDefault();
            }

            return dethi1;
        }

        public static List<DeThi> getAllDeThi()
        {
            List<DeThi> listDeThi = new List<DeThi>();

            using (UserDatabase ud = new UserDatabase(StrConnectionString))
            {
                IQueryable<DeThi> dethiQuery = from dt in ud.DeThiTable select dt;
                listDeThi = dethiQuery.ToList<DeThi>();
            }

            return listDeThi;
        }

        public static List<long> GetListKeyCauHoiByDeThiId(int deThiID)
        {
            using (var userDb = new UserDatabase(StrConnectionString))
            {
                var queryKeyCauHoi = from deThiCauHoi in userDb.DeThiCauHoiTable
                                     where deThiCauHoi.DeThiID == deThiID
                                     select deThiCauHoi.CauHoiID;
                return queryKeyCauHoi.ToList();
            }
        } 

        public static CauHoi GetCauHoiByCauHoiId(long cauHoiId)
        {
            using (var userDb = new UserDatabase(StrConnectionString))
            {
                var cauHoi = userDb.CauHoiTable.Single(p => p.CauHoiID == cauHoiId);
                return cauHoi;
            }
        }

        public static List<DapAn> GetDapAnbyListCauHoiID(List<CauHoi> listCauHoi)
        {
            List<DapAn> listDapAn = new List<DapAn>();

            using (UserDatabase ud = new UserDatabase(StrConnectionString))
            {
                foreach (CauHoi ch in listCauHoi) 
                {
                    IQueryable<DapAn> dapAnQuery = from da in ud.DapAnTable where da.CauHoiID == da.CauHoiID select da;

                    List<DapAn> lda = dapAnQuery.ToList<DapAn>();

                    foreach (DapAn da1 in lda)
                    {
                        listDapAn.Add(da1);
                    }
                } 
            }
            return listDapAn;
        }

        public static void UpdateDeThiDB(DeThi dethi)
        {
            using (var ud = new UserDatabase(StrConnectionString))
            {
                var dethiQuery = from dt in ud.DeThiTable where dt.DeThiID == dethi.DeThiID select dt;               

                try
                {
                    if (!dethiQuery.Any())
                    {
                        ud.DeThiTable.InsertOnSubmit(dethi);
                        ud.SubmitChanges();
                    }
                    else
                    {
                        var dt1 = dethiQuery.First();
                        dt1 = dethi;
                        ud.SubmitChanges();
                    }
                    
                }
                catch (Exception e)
                {
                    ud.DeThiTable.InsertOnSubmit(dethi);
                    ud.SubmitChanges();
                }             
            }
        }

        public static void InsertCauHoi(CauHoi cauHoi) 
        {
            using (var userDb = new UserDatabase(StrConnectionString))
            {
                if (userDb.CauHoiTable.Contains(cauHoi)) return;
                userDb.CauHoiTable.InsertOnSubmit(cauHoi);
                userDb.SubmitChanges();
            }
        }

        public static void InsertDapAn(DapAn dapAn)
        {
            using (var userDb = new UserDatabase(StrConnectionString))
            {
                if (userDb.DapAnTable.Contains(dapAn))
                {
                    return;
                }
                userDb.DapAnTable.InsertOnSubmit(dapAn);
                userDb.SubmitChanges();
            }
        }

        public static void InsertListCauHoi(List<CauHoi> listCauHoi)
        {
            using (var ud = new UserDatabase(StrConnectionString))
            {
                var cauhoiQuery = from ch in ud.CauHoiTable select ch.CauHoiID;

                var listCHID = cauhoiQuery.ToList<long>();               

                foreach (CauHoi ch in listCauHoi)
                {
                    if (!listCHID.Contains(ch.CauHoiID))
                    {
                        ud.CauHoiTable.InsertOnSubmit(ch);
                        ud.SubmitChanges();
                    }
                }
            }
        }

        public static void InsertListDapAn(List<DapAn> listDapAn)
        {
            using (var ud = new UserDatabase(StrConnectionString))
            {
                var dapAnQuery = from da in ud.DapAnTable select da.DapAnID;

                var listDAID = dapAnQuery.ToList<long>();

                foreach (var da in listDapAn)
                {
                    if (!listDAID.Contains(da.DapAnID))
                    {
                        ud.DapAnTable.InsertOnSubmit(da);
                        ud.SubmitChanges();
                    }
                }
            }
        }


        public static List<DapAn> GetListDapAnByCauHoiId(long cauHoiId)
        {
            using (var userDb = new UserDatabase(StrConnectionString))
            {
                var queryListDapAn = from dapAn in userDb.DapAnTable
                                     where dapAn.CauHoiID == cauHoiId
                                     select dapAn;
                return queryListDapAn.ToList();
            }
        } 

        public static List<DeThi> GetListDeThiByMonHoc(int mon)
        {
            using (var userDb = new UserDatabase(StrConnectionString))
            {
                var queryListDeThi = from deThi in userDb.DeThiTable where deThi.MonHocID == mon select deThi;
                return queryListDeThi.ToList();
            }
        } 

        public static void InsertDeThi(DeThi deThi)
        {
            using (var userDb = new UserDatabase(StrConnectionString))
            {
                if (userDb.DeThiTable.Contains(deThi))
                {
                    return;
                }
                userDb.DeThiTable.InsertOnSubmit(deThi);
                userDb.SubmitChanges();
            }
        }

        public static void InsertDeThiCauHoi(DeThiCauHoi deThiCauHoi)
        {
            using (var userDb = new UserDatabase(StrConnectionString))
            {
                if (userDb.DeThiCauHoiTable.Contains(deThiCauHoi))
                {
                    return;
                }
                userDb.DeThiCauHoiTable.InsertOnSubmit(deThiCauHoi);
                userDb.SubmitChanges();
            }
        }

        public static void DeleteDeThi(int deThiId)
        {
            using (var userDb = new UserDatabase(StrConnectionString))
            {
                var deThiCauhoi = from deThiCauHoiEntity in userDb.DeThiCauHoiTable
                                  where deThiCauHoiEntity.DeThiID == deThiId
                                  select deThiCauHoiEntity;
                foreach (var deThi in deThiCauhoi)
                {
                    userDb.DeThiCauHoiTable.DeleteOnSubmit(deThi);
                }
                var dethi = userDb.DeThiTable.Single(p => p.DeThiID == deThiId);
                userDb.DeThiTable.DeleteOnSubmit(dethi);
                userDb.SubmitChanges();
            }
        }
    }
}
